How to optimize this query


SELECT  id,count(distinct col1) col1,count(distinct col2) col2,count(distinct col3) col3
from table
group by id


Thanks
V

I would try

Clustered Index on id

3 non clustered indexes , 1 for each of the columns col1, col2, col3

How big is a table? What indexes does  the table have? Take a look into indexing view.

https://technet.microsoft.com/en-us/library/dd171921(v=sql.100).aspx

Hi,

Please provide some details about the table schema ,whether it is a transaction table , Query  Execution plan you are getting right now  and also the existing indexes on it .

Regards,